#!/bin/bash
APP=edu2077

# 如果输入的日期按照取输入日期；如果没输入日期取当前时间的前一天
if [ -n "$2" ] ;then
    do_date=$2
else 
    do_date=`date -d "-1 day" +%F`
fi

dws_course_user_rate_1d="
set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table ${APP}.dws_course_user_rate_1d partition(dt='$do_date')
select
course_id,
count(*)     wankesishu,
count(distinct (user_id))       wnkerenshu
from
(select
       user_id,
       course_id,
      sum(play_sec)   shichang,
     max(position_sec) jindu,
     dt
from ${APP}.dwd_video_user_chapter_process_inc
where dt='$do_date'
group by user_id, course_id,dt)t1
where shichang>=810 and jindu>=810
group by course_id,dt;
"
dws_interaction_course_review_1d="
insert overwrite table ${APP}.dws_interaction_course_review_1d partition(dt='$do_date')
select
    course_id,
    course_name,
    round(avg(review_stars),1) avg_score_1d,
    count(distinct user_id) user_num_1d,
    round(sum(if(review_stars=5,1,0)) / count(distinct user_id),2) positive_ratings_1d
from
(
    select distinct
        course_id,
        course_name,
        review_stars,
        user_id
    from
    (
        select
            course_id,
            review_stars,
            user_id
        from ${APP}.dwd_interaction_course_comment_inc
        where dt='$do_date'
    ) t0
    left join
    (
        select
            id,
            course_name
        from ${APP}.dim_course_full
        where dt='$do_date'
    ) t2
    on t0.course_id=t2.id
) t1
group by course_id, course_name;
"
dws_trade_category_order_1d="
insert overwrite table ${APP}.dws_trade_category_order_1d partition(dt='$do_date')
select
       category_id,
       category_name,
       count(distinct (order_id)) order_count_1d,
       count(distinct (user_id)) order_user_num_1d,
       sum(split_payment_amount) order_total_amount_1d
from
(
    select
        course_id,
        order_id,
        user_id,
        split_payment_amount
    from ${APP}.dwd_trade_pay_detail_suc_inc
    where dt='$do_date'
)t1
left join
(
    select
        id,
        category_id,
        category_name
    from ${APP}.dim_course_full
    where dt='$do_date'
) t2
on t1.course_id = t2.id
group by category_id,category_name;
"

dws_trade_course_order_1d="
insert overwrite table ${APP}.dws_trade_course_order_1d partition(dt='$do_date')
select
    course_id,
    course_name,
    count(distinct (order_id)) order_count_1d,
    count(distinct (user_id)) order_user_num_1d,
    sum(split_payment_amount) order_total_amount_1d
from
(
    select
        course_id,
        order_id,
        user_id,
        split_payment_amount
    from ${APP}.dwd_trade_pay_detail_suc_inc
    where dt='$do_date'
) t1
left join
(
    select
        id,
        course_name
    from ${APP}.dim_course_full
    where dt='$do_date'
) t2
on t1.course_id = t2.id
group by course_id,course_name;
"
dws_trade_payment_1d="
insert overwrite table ${APP}.dws_trade_payment_1d partition(dt='$do_date')
select
    count(distinct (user_id)) payment_count_1d,
    sum(course_num) payment_num_1d,
    sum(split_payment_amount) payment_amount_1d
from ${APP}.dwd_trade_pay_detail_suc_inc
where dt='$do_date';
"

dws_trade_province_order_1d="
insert overwrite table ${APP}.dws_trade_province_order_1d partition(dt='$do_date')
select
    province_id,
    province_name,
    area_code,
    iso_code,
    iso_3166_2,
    order_count_1d,
    order_user_num_1d,
    order_original_amount_1d,
    coupon_reduce_amount_1d,
    order_total_amount_1d
from
(
    select
        province_id,
        count(distinct(order_id)) order_count_1d,
        count(distinct (user_id)) order_user_num_1d,
        sum(split_original_amount) order_original_amount_1d,
        sum(nvl(split_coupon_amount,0)) coupon_reduce_amount_1d,
        sum(split_payment_amount) order_total_amount_1d
    from ${APP}.dwd_trade_pay_detail_suc_inc
    where dt='$do_date'
    group by province_id
)o
left join
(
    select
        id,
        name province_name,
        area_code,
        iso_code,
        iso_3166_2
    from ${APP}.dim_province_full
    where dt='$do_date'
)p
on o.province_id=p.id;
"

dws_trade_subject_order_1d="
insert overwrite table ${APP}.dws_trade_subject_order_1d partition(dt='$do_date')
select
       subject_id,
       subject_name,
       count(distinct (order_id)) order_count_1d,
       count(distinct (user_id)) order_user_num_1d,
       sum(split_payment_amount) order_total_amount_1d
from
(
    select
        course_id,
        order_id,
        user_id,
        split_payment_amount
    from ${APP}.dwd_trade_pay_detail_suc_inc
    where dt='$do_date'
)t1
left join
(
    select
        id,
        subject_id,
        subject_name
    from ${APP}.dim_course_full
    where dt='$do_date'
) t2
on t1.course_id = t2.id
group by subject_id,subject_name;
"

dws_trade_user_cart_add_1d="
insert overwrite table ${APP}.dws_trade_user_cart_add_1d partition(dt='$do_date')
select
    user_id,
    count(*),
    sum(1)
from ${APP}.dwd_trade_cart_add_inc
where dt='$do_date'
group by user_id;
"
dws_trade_user_order_1d="
insert overwrite table ${APP}.dws_trade_user_order_1d partition(dt='$do_date')
select
    user_id,
    count(distinct(order_id)),
    sum(1),
    sum(origin_amount),
    sum(final_amount)
from ${APP}.dwd_trade_order_detail_inc
where dt='$do_date'
group by user_id;
"
dws_traffic_page_visitor_page_view_1d="
insert overwrite table ${APP}.dws_traffic_page_visitor_page_view_1d partition(dt='$do_date')
select
    mid_id,
    brand,
    model,
    operate_system,
    page_id,
    sum(during_time),
    count(*)
from ${APP}.dwd_traffic_page_view_inc
where dt='$do_date'
group by mid_id,brand,model,operate_system,page_id;
"

dws_traffic_session_page_view_1d="
insert overwrite table ${APP}.dws_traffic_session_page_view_1d partition(dt='$do_date')
select
    session_id,
    mid_id,
    brand,
    model,
    operate_system,
    version_code,
    channel,
    source_id,
    source_site,
    sum(during_time),
    count(*)
from  ${APP}.dwd_traffic_page_view_inc
where dt='$do_date'
group by session_id,mid_id,brand,model,operate_system,version_code,channel,source_id,source_site;
"

dws_video_audition_play_1d="
insert overwrite table ${APP}.dws_video_audition_play_1d partition(dt='$do_date')
select
       course_id,
       course_name,
       subject_id,
       subject_name,
       category_id,
       category_name,
       count(user_id) user_count,
       sum(order_num) audition_sum
from (
         select t1.user_id,
                t1.course_id,
                course_name,
                subject_id,
                subject_name,
                category_id,
                category_name,
                if(t2.order_id is not null, 1, 0) order_num
         from (
                  select user_id,
                         course_id,
                         course_name,
                         subject_id,
                         subject_name,
                         category_id,
                         category_name
                  from ${APP}.dwd_video_audition_play_inc
                  where dt = '$do_date'
                    and is_first = 1
              ) t1
                  left join (
             select user_id,
                    course_id,
                    id order_id
             from  ${APP}.dwd_trade_order_detail_inc
             where dt = '$do_date'
         ) t2 on t1.user_id = t2.user_id and t1.course_id = t2.course_id
     ) t3
group by course_id,course_name,subject_id,subject_name,category_id,category_name
order by course_id;
"

dws_video_user_rate_1d="
insert overwrite table ${APP}.dws_video_user_rate_1d partition(dt='$do_date')
select
    user_id,
    course_id,
    chapter_id,
    t1.video_id,
    ps
from
(
    select
common.uid  user_id,
sum(appvideo.play_sec )   ps,
appvideo.video_id video_id,
max(from_unixtime(cast((ts/1000)as bigint),"yyyy-MM-dd HH:mm:ss")) creat_time
    from ${APP}.ods_log_inc
  where dt='$do_date'
and appvideo.play_sec is not null and appvideo.video_id is not null
    group by common.uid,appvideo.video_id
)t1
left join
(
    select
        video_id,
        chapter_num    chapter_id,
        id  course_id
    from ${APP}.dim_course_full
    where dt='$do_date'
)t2
on t1.video_id=t2.video_id;
"
case $1 in
    "dws_course_user_rate_1d" )
        hive -e "$dws_course_user_rate_1d"
    ;; 
	"dws_interaction_course_review_1d" )
        hive -e "$dws_interaction_course_review_1d"
    ;; 
	"dws_trade_category_order_1d" )
        hive -e "$dws_trade_category_order_1d"
    ;; 
	"dws_trade_course_order_1d" )
        hive -e "$dws_trade_course_order_1d"
    ;;  
	"dws_trade_payment_1d" )
        hive -e "$dws_trade_payment_1d"
    ;;
    "dws_trade_province_order_1d" )
        hive -e "$dws_trade_province_order_1d"
    ;;
    "dws_trade_subject_order_1d" )
        hive -e "$dws_trade_subject_order_1d"
    ;;
    "dws_trade_user_cart_add_1d" )
        hive -e "$dws_trade_user_cart_add_1d"
    ;;
    "dws_trade_user_order_1d" )
        hive -e "$dws_trade_user_order_1d"
    ;;
    "dws_traffic_page_visitor_page_view_1d" )
        hive -e "$dws_traffic_page_visitor_page_view_1d"
    ;;
    "dws_traffic_session_page_view_1d" )
        hive -e "$dws_traffic_session_page_view_1d"
    ;;
    "dws_video_audition_play_1d" )
        hive -e "$dws_video_audition_play_1d"
    ;;
    "dws_video_user_rate_1d" )
        hive -e "$dws_video_user_rate_1d"
    ;;
    "all" )
        hive -e "$dws_course_user_rate_1d$dws_interaction_course_review_1d$dws_trade_category_order_1d$dws_trade_course_order_1d$dws_trade_payment_1d$dws_trade_province_order_1d$dws_trade_subject_order_1d$dws_trade_user_cart_add_1d$dws_trade_user_order_1d$dws_traffic_page_visitor_page_view_1d$dws_traffic_session_page_view_1d$dws_video_audition_play_1d$dws_video_user_rate_1d"
    ;;
esac

